<?php
if(!empty($_SERVER["QUERY_STRING"])){
	echo "error";
	exit;
}
date_default_timezone_set("Asia/Shanghai");
include 'db.php';//数据库连接	
$today = date('Y-m-d',time()-3600*24*1);

$var_today = "$today 投标英雄榜";

$limit = 6;
$start = strtotime($today." 00:00:00");
$end = strtotime($today." 00:00:00")+3600*24;

//echo $start."-------------".$end;
$sql = "SELECT p1.user_id,SUM(p1.account) AS money,p2.username 
	FROM ych_borrow_tender AS p1 
	LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_borrow AS p3 ON p1.borrow_id=p3.id 
	WHERE p1.add_time>=$start AND p1.add_time<=$end AND p3.ftype=4 
	GROUP BY p1.user_id";
$lzdata = db_fetch_arrays($sql,$conn);
$data = array();
foreach ($lzdata as $val){
	$data[$val['user_id']] = $val;
}

$sql = "select p1.user_id,sum(p1.account) as money,p2.username from ych_yjb_tender as p1
	left join ych_user as p2 on p1.user_id=p2.user_id 
	where p1.add_time>=$start and p1.add_time<=$end and p1.time_limit_day=30 
	group by p1.user_id";
$yjbdata = db_fetch_arrays($sql,$conn);
foreach ($yjbdata as $v){
	if (isset($data[$v['user_id']])){
		$data[$v['user_id']]['money'] = $data[$v['user_id']]['money'] + $v['money'];
	}else{
		$data[$v['user_id']] = $v;
	}
}
$data = array_sort($data, 'money', 'desc');
$data = array_slice($data, 0, $limit);


///////////////////今日统计//////////////////////
$start = strtotime(date('Y-m-d'));
$end = $start+(3600*24);

$sql = "SELECT p1.user_id,SUM(p1.account) AS money,p2.username 
	FROM ych_borrow_tender AS p1 
	LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_borrow AS p3 ON p1.borrow_id=p3.id 
	WHERE p1.add_time>=$start AND p1.add_time<=$end AND p3.ftype=4 
	GROUP BY p1.user_id";

$lzdata2 = db_fetch_arrays($sql,$conn);
$data2 = array();
foreach ($lzdata2 as $val){
	$data2[$val['user_id']] = $val;
}

$sql = "select p1.user_id,sum(p1.account) as money,p2.username from ych_yjb_tender as p1
	left join ych_user as p2 on p1.user_id=p2.user_id 
	where p1.add_time>=$start and p1.add_time<=$end and p1.time_limit_day=30 
	group by p1.user_id";
$yjbdata2 = db_fetch_arrays($sql,$conn);
foreach ($yjbdata2 as $v){
	if (isset($data2[$v['user_id']])){
		$data2[$v['user_id']]['money'] = $data2[$v['user_id']]['money'] + $v['money'];
	}else{
		$data2[$v['user_id']] = $v;
	}
}
$data2 = array_sort($data2, 'money', 'desc');
$data2 = array_slice($data2, 0, $limit);


///////////////////月统计//////////////////////
$month_first = date('Y-m-01');
$up_month_first = date("Y-m-d",strtotime("$month_first -1 month"));
$up_month_start = strtotime($up_month_first);
$up_month_end = strtotime($month_first)-1;

//$start = strtotime("2014-06-01 00:00:00");
//$end = strtotime("2014-07-01 00:00:00");

$sql = "SELECT p1.user_id,SUM(p1.account) AS money,p2.username 
	FROM ych_borrow_tender AS p1 
	LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_borrow AS p3 ON p1.borrow_id=p3.id 
	WHERE p1.add_time>=$up_month_start AND p1.add_time<=$up_month_end AND p3.ftype=4 
	GROUP BY p1.user_id ";

//$data3 = db_fetch_arrays($sql,$conn);
$lzdata3 = db_fetch_arrays($sql,$conn);
$data3 = array();
foreach ($lzdata3 as $val){
	$data3[$val['user_id']] = $val;
}

$sql = "select p1.user_id,sum(p1.account) as money,p2.username from ych_yjb_tender as p1
	left join ych_user as p2 on p1.user_id=p2.user_id 
	where p1.add_time>=$up_month_start and p1.add_time<=$up_month_end and p1.time_limit_day=30 
	group by p1.user_id";
$yjbdata3 = db_fetch_arrays($sql,$conn);
foreach ($yjbdata3 as $v){
	if (isset($data3[$v['user_id']])){
		$data3[$v['user_id']]['money'] = $data3[$v['user_id']]['money'] + $v['money'];
	}else{
		$data3[$v['user_id']] = $v;
	}
}
$data3 = array_sort($data3, 'money', 'desc');
$data3 = array_slice($data3, 0, $limit);



///////////////////当月统计//////////////////////
$date = date('Y-m-').'01';
$start = strtotime($date);
$end = time();

$sql = "SELECT p1.user_id,SUM(p1.account) AS money,p2.username 
	FROM ych_borrow_tender AS p1 
	LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_borrow AS p3 ON p1.borrow_id=p3.id 
	WHERE p1.add_time>=$start AND p1.add_time<=$end AND p3.ftype=4 
	GROUP BY p1.user_id";

//$data4 = db_fetch_arrays($sql,$conn);
$lzdata4 = db_fetch_arrays($sql,$conn);
$data4 = array();
foreach ($lzdata4 as $val){
	$data4[$val['user_id']] = $val;
}

$sql = "select p1.user_id,sum(p1.account) as money,p2.username from ych_yjb_tender as p1
	left join ych_user as p2 on p1.user_id=p2.user_id 
	where p1.add_time>=$start and p1.add_time<=$end and p1.time_limit_day=30 
	group by p1.user_id";
$yjbdata4 = db_fetch_arrays($sql,$conn);
foreach ($yjbdata4 as $v){
	if (isset($data4[$v['user_id']])){
		$data4[$v['user_id']]['money'] = $data4[$v['user_id']]['money'] + $v['money'];
	}else{
		$data4[$v['user_id']] = $v;
	}
}
$data4 = array_sort($data4, 'money', 'desc');
$data4 = array_slice($data4, 0, $limit);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>投资排行榜</title>
<style>

.phb{width:1000px; margin:0 auto;overflow:hidden;}
.phb_title{text-align:center; margin-top:30px; }
.phb_title img{}
.phb_title h2{background: url("phb-title.png") no-repeat scroll 370px center rgba(0, 0, 0, 0);font-size:24px; color:#305469; font-weight:bold; line-height:50px;}

.phb_table{float:left; width:450px; margin-top:40px;margin-left:25px;margin-right:25px;}
.phb_table h2{font-size:16px;color:#305469;text-align:center;}

table{ border-collapse:collapse; margin:1em 0; }
.phb_table table tr th{background:#328aa4; color:#fff; text-align:center; padding:.5em; border:1px solid #fff;}
.phb_table table tr td{  text-align:center; padding:.5em; border:1px solid #fff;}
/* table styles */

</style>
</head>


<body>
<div class="phb">
<div class="phb_title">
   <h2>投资英雄排行榜</h2>
</div>
  <div class="phb_table">
    <h2><?php echo $var_today;?></h2>
    <table width="450" cellpadding="0" cellspacing="0">
			<tr>
				<th>排名</th>
				<th>用户名</th>
				<th>投标累计金额</th>
				<th>日投标奖励</th>
            </tr>
            <?php 
			$i = 1;
			foreach ($data as $val){
			?>
			<?php if($i < 4){?>
			<tr>
				<td height="36"><img src="phb<?php echo $i; ?>.png" width="36" height="36"  alt="1"/></td>
				<td><?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?></td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
				<td>
				<?php 
				if ($val['money']>=500000){ 
					echo '518';
				}elseif ($val['money']>=300000){
					echo '288';	
				}elseif ($val['money']>=200000){
					echo '188';	
				}elseif ($val['money']>=100000){
					echo '128';	
				}elseif ($val['money']>=50000){
					echo '88';	
				}else{
					echo '无';	
				}
				?>
				</td>
           </tr>		
           		<?php }else{?>
			<tr>
				<td height="36" style="font-weight:bold ; color:#9f2222; " ><?php echo $i; ?></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
				<td>
				<?php 
				if ($val['money']>=500000){ 
					echo '518';
				}elseif ($val['money']>=300000){
					echo '288';	
				}elseif ($val['money']>=200000){
					echo '188';	
				}elseif ($val['money']>=100000){
					echo '128';	
				}elseif ($val['money']>=50000){
					echo '88';	
				}else{
					echo '无';	
				}
				?>
				</td>
           </tr>
           <?php }?>
         	<?php 
			$i=$i+1;
			}
			?>
</table>		
  </div>
  
  <div class="phb_table">
    <h2><?php echo date('Y-m-d')." 投标英雄榜";?></h2>
    <table width="450" cellpadding="0" cellspacing="0">
			<tr>
				<th>排名</th>
				<th>用户名</th>
				<th>投标累计金额</th>
            </tr>
            <?php 
			$i = 1;
			foreach ($data2 as $val){
			?>
			<?php if($i < 4){?>
			<tr>
				<td height="36"><img src="phb<?php echo $i; ?>.png" width="36" height="36"  alt="1"/></td>
				<td><?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?></td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
           </tr>		
           		<?php }else{?>
			<tr>
				<td height="36" style="font-weight:bold ; color:#9f2222; " ><?php echo $i; ?></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
           </tr>
           <?php }?>
		           <?php 
		$i=$i+1;
		}
		?>
         	
</table>		
  </div>
  </div>
  <div class="phb">
  <div class="phb_table">
    <h2>当月排行榜</h2>
    <table width="450" cellpadding="0" cellspacing="0">
			<tr>
				<th>排名</th>
				<th>用户名</th>
				<th>投标累计金额</th>
            </tr>
            <?php 
			$i = 1;
			foreach ($data4 as $val){
			?>
			<?php if($i < 4){?>
			<tr>
				<td height="36"><img src="phb<?php echo $i; ?>.png" width="36" height="36"  alt="1"/></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
           </tr>		
           		<?php }else{?>
			<tr>
				<td height="36" style="font-weight:bold ; color:#9f2222; " ><?php echo $i; ?></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
           </tr>
           <?php }?>
           <?php 
			$i=$i+1;
			}
			?>
         	
</table>		
  </div>
  
  <div class="phb_table">
    <h2><?php echo date('m',$up_month_start);?>月排行榜</h2>
    <table width="450" cellpadding="0" cellspacing="0">
			<tr>
				<th>排名</th>
				<th>用户名</th>
				<th>投标累计金额</th>
            </tr>
            <?php 
				$i = 1;
				foreach ($data3 as $val){
				?>
				<?php if($i < 4){?>
			<tr>
				<td height="36"><img src="phb<?php echo $i; ?>.png" width="36" height="36"  alt="1"/></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td><?php echo isset($val['money']) ? $val['money'] : 0; ?></td>
           </tr>		
           		<?php }else{?>
			<tr>
				<td height="36" style="font-weight:bold ; color:#9f2222; " ><?php echo $i; ?></td>
				<td> <?php echo isset($val['username']) ? strdisplay_start($val['username'],4) : 0; ?> </td>
				<td> <?php echo isset($val['money']) ? $val['money'] : 0; ?> </td>
           </tr>
           <?php }?>
           <?php 
			$i=$i+1;
			}
			?>
         	
</table>		
  </div>

</div>
</body>
</html>

<?php 
function db_fetch_arrays($sql, $conn) 	{
	$result = mysql_query($sql, $conn);
	$i = 0;
	$_res = array();
	while(@$res = mysql_fetch_array($result,MYSQL_ASSOC)) {
		foreach ($res as $key => $value){
			$_res[$i][$key] = $value;//直接转义
		}
		$i++;
	}
	return $_res;
}

function db_fetch_array($sql,$conn) {
		$result = mysql_query($sql, $conn);
		$res = mysql_fetch_array($result,MYSQL_ASSOC);
		$_res = "";
		if (is_array($res)){
			foreach ($res as $key => $value){
				$_res[$key] =$value;
			}
		}
		$query = $_res;
		return $query;
}
function strdisplay_start($str, $length = 1)
{
	$xing = "**";
	if($str == null || $str == ''){
		return '';
	}
	if(!preg_match("/[^\x80-\xff]/",$str)){
		return mb_substr($str, 0, 1, 'utf-8').$xing;	
	}else{
		return substr($str, 0, $length).$xing;	
	}
}

function array_sort($arr,$keys,$type='asc'){
	$keysvalue = $new_array = array();
	foreach ($arr as $k=>$v){
		$keysvalue[$k] = $v[$keys];
	}
	if($type == 'asc'){
		asort($keysvalue);
	}else{
		arsort($keysvalue);
	}
	reset($keysvalue);
	foreach ($keysvalue as $k=>$v){
		$new_array[$k] = $arr[$k];
	}
	return $new_array;
}
?>